package com.supermap.dataservice.cloud.maker;

import com.supermap.dubbo.api.cloud.maker.AdInfo;
import org.apache.ibatis.annotations.*;
import org.apache.logging.log4j.util.Strings;

import java.util.List;
import java.util.Map;

/**
 * @author aqua
 * @description
 * @date 2019年11月20 14:48
 * @email 1301584033@qq.com
 */
@Mapper
public interface MakerMapper {

    @Select("select group_name,group_concat(distinct id) ids from\n" +
            "(select t2.group_name,t2.id,t3.create_date from  cqpamiss.t_b_productgroup t2\n" +
            "inner join cqpamiss.t_b_pms_product t3 on t2.id = t3.product_group\n" +
            "where t3.product_state = 6 and  t2.content_category = #{templateId}\n" +
            "union \n" +
            "select t2.group_name,t2.id,t3.create_date  from china_pamiss.t_b_productgroup t2 \n" +
            "inner join china_pamiss.t_b_pms_product t3 on t2.id = t3.product_group\n" +
            "where t3.product_state = 6 and t2.content_category = #{templateId}) g\n" +
            "group by group_name order by group_concat(distinct create_date order by create_date desc ) desc")
    List<Map<String,Object>> getProductGroupList86(@Param("templateId")  Integer templateId);

    @Select("select group_name,group_concat(distinct id) ids from\n" +
            "(select t2.group_name,t2.id,t3.create_date from cagmpisards.t_area t1\n" +
            "inner join cqpamiss.t_b_productgroup t2 on find_in_set(t1.areacode,t2.fit_areaid)\n" +
            "inner join cqpamiss.t_b_pms_product t3 on t2.id = t3.product_group\n" +
            "where t3.product_state = 6 and  t2.content_category = #{templateId} and\n" +
            "case \n" +
            "when length(#{areaCode}) = 2 then  t1.areacode like concat(#{areaCode},'%')\n" +
            "when length(#{areaCode}) = 4 then  t1.areacode like concat(#{areaCode},'%') or t1.areacode = left(concat(#{areaCode},'%'),2)\n" +
            "when length(#{areaCode}) = 6 then  t1.areacode like concat(#{areaCode},'%') or t1.areacode = left(concat(#{areaCode},'%'),2) or t1.areacode = left(concat(#{areaCode},'%'),4)\n" +
            "end \n" +
            "union \n" +
            "select t2.group_name,t2.id,t3.create_date  from cagmpisards.t_area t1\n" +
            "inner join china_pamiss.t_b_productgroup t2 on find_in_set(t1.areacode,t2.fit_areaid)\n" +
            "inner join china_pamiss.t_b_pms_product t3 on t2.id = t3.product_group\n" +
            "where t3.product_state = 6 and t2.content_category = #{templateId} and\n" +
            "case \n" +
            "when length(#{areaCode}) = 2 then  t1.areacode like concat(#{areaCode},'%')\n" +
            "when length(#{areaCode}) = 4 then  t1.areacode like concat(#{areaCode},'%') or t1.areacode = left(concat(#{areaCode},'%'),2)\n" +
            "when length(#{areaCode}) = 6 then  t1.areacode like concat(#{areaCode},'%') or t1.areacode = left(concat(#{areaCode},'%'),2) or t1.areacode = left(concat(#{areaCode},'%'),4)\n" +
            "end) g\n" +
            "group by group_name order by group_concat(distinct create_date order by create_date desc ) desc")
    List<Map<String,Object>> getProductGroupList(@Param("areaCode") String areaCode,@Param("templateId")  Integer templateId);
    @Select("select * from\n" +
            "(select date_format(p.create_date,'%Y-%m-%d %H:%i:%S') cdate,p.product_unit departname, concat('/',p.folder_name,'/',p.id,'.html')  path,p.product_mainpic logo ,\n" +
            "p.product_title title,p.product_group_name  ptype,concat('http://www.cqpisa.cn/cq/html','/',p.folder_name,'/',p.id,'.html') productPath,\n" +
            "case\n" +
            "when p.product_mainpic is null then 'http://www.cqpisa.cn/cq/images/product_logo.jpg'\n" +
            "when left(product_mainpic,1) ='/' then concat('http://www.cqpisa.cn/cq',p.product_mainpic)\n" +
            "else\n" +
            "concat('http://www.cqpisa.cn/cq','/',p.product_mainpic)\n" +
            "end logoPath,p.product_summary,p.id," +
            "if(p.product_read_count is null,0,product_read_count) product_read_count," +
            "if(p.product_praise_times is null,0,product_praise_times) product_praise_times, " +
            "if(p.product_ncomment_times is null,0,product_ncomment_times) product_ncomment_times " +
            " from cqpamiss.t_b_pms_product p\n" +
            "where p.product_state = 6 and find_in_set(p.product_group,#{groupIds})\n" +
            "union \n" +
            "select date_format(p.create_date,'%Y-%m-%d %H:%i:%S') cdate, product_unit departname, concat('/',p.folder_name,'/',p.id,'.html')  path,p.product_mainpic logo ,\n" +
            "p.product_title title,p.product_group_name  ptype,concat('http://www.cqpisa.cn/china/html','/',p.folder_name,'/',p.id,'.html' ) productPath,\n" +
            "case\n" +
            "when p.product_mainpic is null then 'http://www.cqpisa.cn/cq/images/product_logo.jpg'\n" +
            "when left(product_mainpic,1) ='/' then concat('http://www.cqpisa.cn/china',p.product_mainpic)\n" +
            "else\n" +
            "concat('http://www.cqpisa.cn/china','/',p.product_mainpic)\n" +
            "end logoPath,p.product_summary,p.id," +
            "if(p.product_read_count is null,0,product_read_count) product_read_count," +
            "if(p.product_praise_times is null,0,product_praise_times) product_praise_times, " +
            "if(p.product_ncomment_times is null,0,product_ncomment_times) product_ncomment_times " +
            "from china_pamiss.t_b_pms_product p\n" +
            "where p.product_state = 6 and find_in_set(p.product_group,#{groupIds})) t\n" +
            "order by t.cdate desc;\n")
    List<Map<String,Object>> getProductList(@Param("groupIds")String groupIds);

    @SelectProvider(type =MakerMapperSQL.class,method = "getUserListSQL")
    List<Map<String,Object>> getUserList(@Param("roleCode")String roleCode,@Param("userName") String userName, @Param("areaCode") String areaCode,@Param("page") Integer page,@Param("size") Integer size);

    @Insert("insert into t_user_click_menu(menuCode,userId,areaCode) values (#{menuCode},#{userId},#{areaCode})")
    void putMenuClick(@Param("menuCode") String menuCode,@Param("userId") Integer userId,@Param("areaCode") String areaCode);
    //广告上传相关
    @Insert("insert into t_adinfo(title,areaCode,url,img,state,isuse) values(#{adInfo.title},#{adInfo.areaCode},#{adInfo.url},#{adInfo.img},#{adInfo.state},#{adInfo.isuse})")
    void putAdSlideInfo(@Param("adInfo")AdInfo adInfo);
    @Update("update t_adinfo set isuse=#{adInfo.isuse} where id=#{adInfo.id}")
    void saveAdSlideInfo(@Param("adInfo")AdInfo adInfo);

    @Delete("delete from t_adinfo where id=#{adInfoId}")
    void delAdSlideInfo(@Param("adInfoId") Integer adInfoId);

    @Insert("insert ignore into t_expert_attention(userId,expertId) values(#{userId},#{expertId})")
    void putExpertAttention(@Param("userId")Integer userId,@Param("expertId")Integer expertId);

    @Delete("delete from t_expert_attention where userId=#{userId} and expertId=#{expertId}")
    void delExpertAttention(@Param("userId")Integer userId,@Param("expertId")Integer expertId);
    //获取专家列表关注度
    @SelectProvider(type =MakerMapperSQL.class,method = "getExpertListSQL")
    List<Map<String,Object>> getExpertList(@Param("userId")Integer userId,@Param("expertName") String expertName, @Param("subjectId") Integer subjectId, @Param("areaCode") String areaCode,@Param("state")Integer state,@Param("page") Integer page,@Param("size") Integer size);

    @Select("select s3.sname,s3.code,s3.category,s3.functiontype, s3.url,s3.icon,if(s4.id is null,0,1) iscancel from t_user t1\n" +
            "inner join t_user_role t2 on t1.id =t2.userid\n" +
            "inner join sys_area_role s1 on left(t1.areaCode,2) = s1.areacode and t2.roleid = s1.roleid\n" +
            "inner join sys_area_role_menu_new s2 on s1.id = s2.arearoleid\n" +
            "inner join sys_menu_new s3 on s2.menucode = s3.code\n" +
            "left join sys_user_menu_cancel s4 on s3.code = s4.menucode and t1.id =s4.userid\n" +
            "where t1.id = #{userId} and s3.state = 0 \n" +
            "order by left(s3.code,3),s3.category,s3.order_num")
    List<Map<String,Object>> getOrderMenuByUserId(@Param("userId") Integer userId);

    @Select("            select t1.headline  title,t2.iconurl img,'' url, t1.description as content, 0 as state,t2.standard,t2.measures  from t_warning_info t1\n" +
            "            inner join t_warning_iconurl t2 on t2.eventType=t1.eventType and t1.severity = t2.severity \n" +
            "            where t1.areaCode like concat(#{areaCode},'%') and now() < TIMESTAMP(t1.expires) \n" +
            "            order by t1.effective desc")
    List<Map<String,Object>> getIndexSlideInfo1(@Param("areaCode") String areaCode,@Param("limit")Integer limit);
    //@SelectProvider(type = MakerMapperSQL.class,method = "getAdSlideInfoSQL")

    @Select("select title,img,url,content,state from\n" +
            "            (select title,img ,url,\"\" as content, t.state,t.ctime  from t_adinfo t where t.isuse = 0 and t.state =1   and\n" +
            "\t\t\t\tcase \n" +
            "\t\t\t\twhen #{areaCode} = '86' then 1=1\n" +
            "\t\t\t\twhen length(#{areaCode}) = 2 then areaCode like concat(#{areaCode},'%')\n" +
            "\t\t\t\twhen length(#{areaCode}) = 4 then (areaCode like concat(#{areaCode},'%') or t.areaCode = left(#{areaCode},2))\n" +
            "\t\t\t\twhen length(#{areaCode}) = 6 then (areaCode like concat(#{areaCode},'%') or t.areaCode = left(#{areaCode},2)or t.areaCode = left(#{areaCode},4))\n" +
            "\t\t\t\tend\n" +
            "            union\n" +
            "            select title,img ,url,\"\" as content, t.state,t.ctime  from t_adinfo t where  t.isuse = 0 and t.state =2 \n" +
            "            union \t\t\t\t\t\t\t\n" +
            "\t\t\t\tselect p.product_name title, concat(\"http://www.cqpisa.cn:8030/wp/\",p.img_path) img,\n" +
            "\t\t\t\tcase \n" +
            "\t\t\t\twhen u.role_code = \"QYYH\" then  concat(\"http://www.cqpisa.cn:8030/rfpf/#/mains/\",u.id)\n" +
            "\t\t\t\twhen u.role_code = \"ZMXC\" then  concat(\"http://www.cqpisa.cn:8030/rfpf/#/mains/\",u.id)\n" +
            "\t\t\t\twhen u.role_code = \"EXPERT\" then  concat(\"http://www.cqpisa.cn:8030/zj/#/mains/\",u.id)\n" +
            "\t\t\t\twhen u.role_code = \"HZSYH\" then  concat(\"http://www.cqpisa.cn:8030/hzs/#/mains/\",u.id)\n" +
            "\t\t\t\twhen u.role_code = \"ZZDH\" then  concat(\"http://www.cqpisa.cn:8030/zydh/#/mains/\",u.id)\n" +
            "\t\t\t\twhen u.role_code = \"GXSYH\" then  concat(\"http://www.cqpisa.cn:8030/gxs/#/mains/\",u.id)\n" +
            "\t\t\t\twhen u.role_code = \"JDYH\" then  concat(\"http://www.cqpisa.cn:8030/rfpf/#/mains/\",u.id)\n" +
            "\t\t\t\tend url, \n" +
            "\t\t\t\tp.prodoct_description as content, 1 as state,p.modify_time ctime from  cq_market.product p\n" +
            "            inner join cq_market.user u on p.user_id = u.id\n" +
            "            inner join t_user t on t.loginName =   CONVERT(u.username USING utf8) COLLATE utf8_unicode_ci \n" +
            "            where \n" +
            "           \tcase \n" +
            "\t\t\t\twhen #{areaCode} = '86' then 1=1 \n" +
            "\t\t\t\twhen length(#{areaCode}) = 2 then areaCode like concat(#{areaCode},'%')\n" +
            "\t\t\t\twhen length(#{areaCode}) = 4 then (areaCode like concat(#{areaCode},'%') or t.areaCode = left(#{areaCode},2))\n" +
            "\t\t\t\twhen length(#{areaCode}) = 6 then (areaCode like concat(#{areaCode},'%') or t.areaCode = left(#{areaCode},2)or t.areaCode = left(#{areaCode},4))\n" +
            "\t\t\t\tend            \n" +
            "\t\t\t\t) ttt\n" +
            "            order by state , ctime desc\n" +
            "            limit #{limit}")
    List<Map<String,Object>> getIndexSlideInfo2(@Param("areaCode") String areaCode,@Param("limit")Integer limit);
    @SelectProvider(type = MakerMapperSQL.class,method = "getAdSlideInfoSQL")
    List<Map<String,Object>> getAdSlideInfo(@Param("areaCode") String areaCode, @Param("state") Integer state,@Param("isuse")Integer isuse,@Param("limit")Integer limit);

    @Select("select id," +
            "if(product_read_count is null,0,product_read_count) product_read_count," +
            "if(product_praise_times is null,0,product_praise_times) product_praise_times, " +
            "if(product_ncomment_times is null,0,product_ncomment_times) product_ncomment_times, " +
            " cdate,departname,path,logo,title,summary,ptype,productPath,logoPath from\n" +
            "(select p.id,p.product_read_count,p.product_praise_times,p.product_ncomment_times, date_format(p.create_date,'%Y-%m-%d %H:%i:%S') cdate, p.product_unit departname, concat('/',p.folder_name,'/',p.id,'.html')  path,p.product_mainpic logo ,\n" +
            "p.product_title title, p.product_summary summary,p.product_group_name ptype,concat('http://www.cqpisa.cn/cq/html','/',p.folder_name,'/',p.id,'.html') productPath,\n" +
            "case\n" +
            "when p.product_mainpic is null then 'http://www.cqpisa.cn/cq/images/product_logo.jpg'\n" +
            "when left(product_mainpic,1) ='/' then concat('http://www.cqpisa.cn/cq',p.product_mainpic)\n" +
            "else\n" +
            "concat('http://www.cqpisa.cn/cq','/',p.product_mainpic)\n" +
            "end logoPath  from \n" +
            "(select t2.id  from cagmpisards.t_area t1\n" +
            "inner join cqpamiss.t_b_productgroup t2 on find_in_set(t1.areacode,t2.fit_areaid)\n" +
            "where \n" +
            "case \n" +
            "when #{areaCode}='86' then 1=1  \n" +
            "when length(#{areaCode}) = 2 then  t1.areacode like concat(#{areaCode},'%')\n" +
            "when length(#{areaCode}) = 4 then  t1.areacode like concat(#{areaCode},'%') or t1.areacode = left(concat(#{areaCode},'%'),2)\n" +
            "when length(#{areaCode}) = 6 then  t1.areacode like concat(#{areaCode},'%') or t1.areacode = left(concat(#{areaCode},'%'),2) or t1.areacode = left(concat(#{areaCode},'%'),4)\n" +
            "end \n" +
            "group by t2.id) g\n" +
            "inner join cqpamiss.t_b_pms_product p on g.id = p.product_group and p.product_state = 6\n" +
            "union \n" +
            "select p.id," +
            "if(product_read_count is null,0,product_read_count) product_read_count," +
            "if(product_praise_times is null,0,product_praise_times) product_praise_times, " +
            "if(product_ncomment_times is null,0,product_ncomment_times) product_ncomment_times, " +
            " date_format(p.create_date,'%Y-%m-%d %H:%i:%S') cdate, p.product_unit departname, concat('/',p.folder_name,'/',p.id,'.html')  path,p.product_mainpic logo ,\n" +
            "p.product_title title, p.product_summary summary, p.product_group_name ptype,concat('http://www.cqpisa.cn/china/html','/',p.folder_name,'/',p.id,'.html' ) productPath,\n" +
            "case\n" +
            "when p.product_mainpic is null then 'http://www.cqpisa.cn/cq/images/product_logo.jpg'\n" +
            "when left(product_mainpic,1) ='/' then concat('http://www.cqpisa.cn/china',p.product_mainpic)\n" +
            "else\n" +
            "concat('http://www.cqpisa.cn/china','/',p.product_mainpic)\n" +
            "end logoPath  from \n" +
            "(select t2.id  from cagmpisards.t_area t1\n" +
            "inner join china_pamiss.t_b_productgroup t2 on find_in_set(t1.areacode,t2.fit_areaid)\n" +
            "where \n" +
            "case \n" +
            "when #{areaCode}='86' then 1=1  \n" +
            "when length(#{areaCode}) = 2 then  t1.areacode like concat(#{areaCode},'%')\n" +
            "when length(#{areaCode}) = 4 then  t1.areacode like concat(#{areaCode},'%') or t1.areacode = left(concat(#{areaCode},'%'),2)\n" +
            "when length(#{areaCode}) = 6 then  t1.areacode like concat(#{areaCode},'%') or t1.areacode = left(concat(#{areaCode},'%'),2) or t1.areacode = left(concat(#{areaCode},'%'),4)\n" +
            "end \n" +
            "group by t2.id) g\n" +
            "inner join china_pamiss.t_b_pms_product p on g.id = p.product_group and p.product_state = 6 \n" +
            ") t\n" +
            "order by t.cdate desc limit  #{limit}")
    List<Map<String,Object>> getProductByArea(@Param("areaCode") String areaCode, @Param("limit") Integer limit);

    /* 获取溯源类型列表 */
    @SelectProvider(type =MakerMapperSQL.class,method = "getTraceTypeListSQL")
    List<Map<String,Object>> getTraceTypeList(@Param("areaCode")String areaCode,@Param("userId") String userId, @Param("farm") String farm);

    /* 获取溯源列表 */
    @SelectProvider(type =MakerMapperSQL.class,method = "getTraceListSQL")
    List<Map<String,Object>> getTraceList(@Param("areaCode")String areaCode,@Param("userId") String userId, @Param("farm") String farm, @Param("subject") String subject);

    /* 获取美丽乡村类型列表 */
    @SelectProvider(type =MakerMapperSQL.class,method = "getCountryTypeListSQL")
    List<Map<String,Object>> getCountryTypeList(@Param("areaCode")String areaCode);

    /* 获取美丽乡村列表 */
    @SelectProvider(type =MakerMapperSQL.class,method = "getCountryListSQL")
    List<Map<String,Object>> getCountryList(@Param("areaCode")String areaCode,@Param("tcode") String tcode);

    /* 获取指定区域、日期内分布的预警信息 */
    @Select("select t1.headline,t1.description,t1.sender,t1.msgType,t2.iconurl,t2.standard,t2.measures,t3.longitude lon,t3.latitude lat  from cagmpisards.t_warning_info t1\n" +
            "inner join cagmpisards.t_warning_iconurl t2 on t1.eventType = t2.eventType and t1.severity = t2.severity\n" +
            "inner join cagmpisards.t_area t3 on t1.areaCode = t3.areacode\n" +
            "where  t1.areaCode like concat(#{areaCode},'%')   and t1.expires > #{cdate}  \n")
    List<Map<String,Object>> getWarningList(@Param("areaCode")String areaCode,@Param("cdate") String cdate);

    /* 获取游客模式下菜单 */
    @Select("select s3.code,s3.sname,s3.icon,s3.functiontype,s3.url,s3.category ,0 iscancel  from sys_area_role s1\n" +
            "inner join sys_area_role_menu_new s2 on s2.arearoleid = s1.id\n" +
            "inner join sys_menu_new s3 on s2.menucode = s3.code\n" +
            "where s1.roleid = 1 and s3.state = 0 and s1.areacode = left(#{areaCode},2)\n" +
            "order by left(s3.code,3),s3.category,s3.order_num")
    List<Map<String,Object>> getVisitorMenuList(@Param("areaCode") String areaCode);

    @Select("select  t1.id,t1.username userName,t1.loginName,t3.name roleName,t3.code roleCode,t4.areaCode,t4.areaName from \n" +
            "\n" +
            "(select t1.id,t1.username userName,t1.loginName,t1.areaCode from t_user t1 where  t1.loginName=#{loginName}  and t1.del_flg=0) t1\n" +
            "inner join t_user_role t2\n" +
            "on t1.id=t2.userid \n" +
            "inner join t_role t3\n" +
            "on t2.roleid=t3.id\n" +
            "inner join t_area t4\n" +
            "on t1.areaCode=t4.areacode\n" +
            "\n" +
            "union all\n" +
            "select t1.id,t1.userName,t1.loginName,t4.roleName,t4.roleCode,t1.areaCode,t2.areaName  from \n" +
            "(select t1.id,t1.realname userName,t1.username loginName,t1.areacode from cqpamiss.t_s_base_user t1 where t1.username=#{loginName} ) t1\n" +
            "inner join cqpamiss.t_area t2\n" +
            "on t1.areacode=t2.areacode\n" +
            "inner join cqpamiss.t_s_role_user t3\n" +
            "on t1.id =t3.userid\n" +
            "inner join cqpamiss.t_s_role t4\n" +
            "on t3.roleid=t4.id\n")
    List<Map<String,Object>> login(@Param("loginName") String loginName);

    @UpdateProvider(type =  MakerMapperSQL.class,method = "updateGoodsReadSql")
    Integer updateGoodsRead(@Param("gtype") String gtype,@Param("gid") String gid,@Param("genre") String genre);

    @UpdateProvider(type =  MakerMapperSQL.class,method = "updateCountryReadSql")
    Integer updateCountryRead(@Param("cid") String cid,@Param("ctype") String ctype);

    /* 公众服务产品(阅读、点赞、分享)数统计(省) */
    @SelectProvider(type =MakerMapperSQL.class,method = "getPublicProductCountSQL")
    List<Map<String,Object>> getPublicProductCount(@Param("areaCode")String areaCode,@Param("Ctype")String Ctype,@Param("limit") Integer limit);

    /* 公众服务产品(阅读、点赞、分享)数统计(全国) */
    @SelectProvider(type =MakerMapperSQL.class,method = "getPublicProductCount86SQL")
    List<Map<String,Object>> getPublicProductCount86(@Param("Ctype")String Ctype,@Param("limit") Integer limit);

    /* 好产品访问量、点赞数、分享数统计 */
    @SelectProvider(type =MakerMapperSQL.class,method = "getGoodsReadCountSQL")
    List<Map<String,Object>> getGoodsReadCount(@Param("areaCode")String areaCode,@Param("gtype")String gtype,@Param("limit") Integer limit);

    /* 美丽乡村访问量、点赞数、分享数统计 */
    @SelectProvider(type =MakerMapperSQL.class,method = "getCountryReadCountSQL")
    List<Map<String,Object>> getCountryReadCount(@Param("areaCode")String areaCode,@Param("ctype")String ctype,@Param("limit") Integer limit);

    class  MakerMapperSQL{
        public String getAdSlideInfoSQL (Map map){
            StringBuilder stringBuilder=new StringBuilder();

            String areaCode=(String)map.get("areaCode");
            Integer state =(Integer)map.get("state");
            Integer isuse =(Integer)map.get("isuse");
            Integer limit =(Integer)map.get("limit");
            stringBuilder.append(" select * from t_adinfo t1 where  1=1 ");
            if(Strings.isNotBlank(areaCode)){
                stringBuilder.append(" and t1.areaCode like concat('"+areaCode+"','%') ");
            }
            if(state!=null){
                stringBuilder.append(" and t1.state ="+state+" ");
            }
            if(isuse!=null){
                stringBuilder.append(" and t1.isuse ="+isuse+" ");
            }
            stringBuilder.append(" order by t1.ctime desc limit "+limit+" ");
            return  stringBuilder.toString();
        }

        public String getUserListSQL(Map map){
            StringBuilder stringBuilder=new StringBuilder();
            String userName=(String)map.get("userName");
            String  roleCode=(String)map.get("roleCode");
            String areaCode=(String)map.get("areaCode");
            Integer page= 0;
            Integer size =0;
           try{
               page =(Integer)map.get("page");
               size =(Integer)map.get("size");
               page=(page-1)*size;
           }catch (Exception ex){
               page= null;
           }

            stringBuilder.append("select t10.areaname,t2.id as userId,v1.score,t2.username as name,t2.mobile,t2.address,t2.email,concat('http://www.cqpisa.cn/ChinaAPP/',t2.HeadImage) AS logo,t2.Introduction as intro,t2.latitude as lat,t2.longitude as lon,t1.url from t_user t2\n" +
                    "left join t_company t1\n" +
                    "on t1.userid=t2.id and t2.del_flg=0\n" +
                    "inner join t_user_role t3\n" +
                    "on t2.id=t3.userid\n" +
                    "inner join t_role t4\n" +
                    "on t3.roleid=t4.id" +
                    " inner join t_area t10\n" +
                    " on t2.areaCode =t10.areacode " +
                    " inner join v_user_score v1 on t2.id=v1.userid " +
                    " where  t2.del_flg=0 ");
            if(Strings.isNotBlank(areaCode) && !"86".equals(areaCode)){
                stringBuilder.append(" and t2.areaCode like concat('"+areaCode+"','%')  and  v1.areaCode like concat('"+areaCode+"','%') ");
            }
            if(Strings.isNotBlank(roleCode)){
                stringBuilder.append(" and find_in_set(t4.code, "+roleCode+") and find_in_set( v1.code,"+roleCode+")");
            }
            else
            {
                stringBuilder.append(" and t4.code in (1,2,4,6) and v1.code in (1,2,4,6)  ");
            }
            if(Strings.isNotBlank(userName)){
                stringBuilder.append(" and t2.username like concat('"+userName+"','%') ");
            }
            stringBuilder.append(" order by v1.score desc ");
            if(page != null)
            {
                stringBuilder.append(" limit "+ page+","+size+";");
            }
            return  stringBuilder.toString();
        }

        public String  getExpertListSQL(Map map){
            StringBuilder stringBuilder=new StringBuilder();
            Integer userId=(Integer)map.get("userId");
            String expertName=(String)map.get("expertName");
            Integer subjectId=(Integer)map.get("subjectId");
            String areaCode=(String)map.get("areaCode");
            Integer page =(Integer)map.get("page");
            Integer size =(Integer)map.get("size");
            page=(page-1)*size;
            Integer state=(Integer)map.get("state");
            stringBuilder.append("select t1.id,t2.username as name,concat('http://www.cqpisa.cn/ChinaAPP/',t2.HeadImage) as img,\n" +
                    "t2.mobile,if(isnull(t1.unit),\"\",t1.unit) as unit,v1.score  ,\n" +
                    "(select group_concat(s2.name) from t_expert_subject e1\n" +
                    "inner join t_subject s2\n" +
                    "on e1.subjectId=s2.id where  e1.expertId=t1.id) as begood,\n" +
                    "(select count(1) from t_expert_attention where userId="+userId+" and expertId=t1.id ) as attention\n" +
                    " from t_expertinfo t1\n" +
                    "inner join t_user t2\n" +
                    "on t1.userid=t2.id and t1.del_flg=0\n" +
                    "inner join t_expert_subject t3\n" +
                    "on t1.id=t3.expertId " +
                    "inner join v_user_score v1 on t1.userid=v1.userid and v1.code='EXPERT'  ");
            if(state!=null&&state==1){
                stringBuilder.append(" inner join t_expert_attention t4\n" +
                        "on t4.expertId=t1.id  and t4.userId="+userId+" ");
            }
            stringBuilder.append(" where 1=1 ");
            if(Strings.isNotBlank(areaCode) && !"86".equals(areaCode)){
                stringBuilder.append(" and t2.areaCode like concat('"+areaCode+"','%') and v1.areaCode like concat('"+areaCode+"','%')  ");
            }
            if(Strings.isNotBlank(expertName)){
                stringBuilder.append(" and t2.username like concat('%','"+expertName+"','%') ");
            }
            if(subjectId!=null){
                stringBuilder.append(" and t3.subjectId="+subjectId+" ");
            }
            stringBuilder.append("  order by v1.score desc limit "+page+","+size+"");
            return  stringBuilder.toString();
        }

        /* 获取溯源列表类型 */
        public String  getTraceTypeListSQL(Map map) {
            StringBuilder stringBuilder=new StringBuilder();
            String userId=(String)map.get("userId");
            String areaCode=(String)map.get("areaCode");
            String farm =(String)map.get("farm");
            stringBuilder.append(" select * from (\n");
            stringBuilder.append("select  t6.name,t6.id from t_traceability t1\n" +
                    "inner join t_traceability_audit t2 on t1.id = t2.tratid\n" +
                    "inner join t_farmcrops t3 on t1.farm = t3.farmland\n" +
                    "inner join t_crop t4 on t3.crop = t4.code\n" +
                    "inner join t_subject_crops t5 on t5.crop = t4.parentid\n" +
                    "inner join t_subject t6 on t5.subject = t6.id\n" +
                    "inner join t_farmland t7 on t1.farm = t7.id\n" +
                    "where t2.statue =1 and t6.del_flg = 0 and t7.del_flg = 0 ");
            if(userId!=null){
                stringBuilder.append("\n and t7.userid = "+userId+" ");
            }
            if(farm!=null){
                stringBuilder.append("\n and t7.id = "+farm+" ");
            }
            if(areaCode!=null && !"86".equals(areaCode)) {
                stringBuilder.append("\n and t7.code like concat('" + areaCode + "','%') ");
            }
            stringBuilder.append("\n union \n");
            stringBuilder.append("select t4.name ,t4.id from t_goodproduct t1\n" +
                    "inner join t_crop t2 on t1.crop = t2.code\n" +
                    "inner join t_subject_crops t3 on t2.parentid = t3.crop\n" +
                    "inner join t_subject t4 on t4.id = t3.subject\n") ;
            if(areaCode!=null&& !"86".equals(areaCode)) {
                stringBuilder.append("where t1.areacode like concat('" + areaCode + "','%') ");
            }

            stringBuilder.append(")g\n  group by g.id;");
            return  stringBuilder.toString();
        }

        /* 获取好产品列表 */
        public String  getTraceListSQL(Map map) {
            StringBuilder stringBuilder=new StringBuilder();
            String userId=(String)map.get("userId");
            String areaCode=(String)map.get("areaCode");
            String farm =(String)map.get("farm");
            String subject =(String)map.get("subject");
            stringBuilder.append(" select * from (\n");
            stringBuilder.append("select t1.id,  t1.name, DATE_FORMAT(t1.createtime, '%Y-%m-%d') createtime,t4.name cname,t8.showName," +
                    "t9.areaname,t8.mobile,t1.weburl,t1.content,t1.url,t1.url qrcode,t5.subject,t7.longitude lon," +
                    "t7.latitude lat,t1.readtimes ,t1.praisetimes,t1.ncommenttimes,1 as gtype  from t_traceability t1\n" +
                    "inner join t_traceability_audit t2 on t1.id = t2.tratid\n" +
                    "inner join t_farmcrops t3 on t1.farm = t3.farmland\n" +
                    "inner join t_crop t4 on t3.crop = t4.code\n" +
                    "inner join t_subject_crops t5 on t5.crop = t4.parentid\n" +
                    "inner join t_farmland t7 on t1.farm = t7.id\n" +
                    "inner join t_user t8 on t7.userId = t8.id\n" +
                    "inner join t_area t9 on t7.code = t9.areacode\n" +
                    "where t2.statue =1  and t7.del_flg = 0 and t8.del_flg =0  ");

            if(userId!=null){
                stringBuilder.append(" and t8.userId = "+userId+" ");
            }
            if(farm!=null){
                stringBuilder.append(" and t7.id = "+farm+" ");
            }
            if(subject!=null){
                stringBuilder.append(" and t5.subject = "+subject+" ");
            }

            if(areaCode!=null && !"86".equals(areaCode)){
                stringBuilder.append(" and t7.code like concat('" + areaCode + "','%') ");
                stringBuilder.append(" and t9.areacode like concat('" + areaCode + "','%') ");
            }
            stringBuilder.append(" union \n");
            stringBuilder.append("select t1.id, t1.name, DATE_FORMAT(now(), '%Y-%m-%d') createtime,t1.name cname,t1.name showName," +
                    "t5.areaname,'' mobile,t1.url weburl,t1.intr content,t1.img url,t1.qrcode,t3.subject," +
                    "t5.longitude lon,t5.latitude lat,t1.readtimes ,t1.praisetimes,t1.ncommenttimes,2 as gtype from t_goodproduct t1\n" +
                    "inner join t_crop t2 on t1.crop = t2.code\n" +
                    "inner join t_subject_crops t3 on t2.parentid = t3.crop\n" +
                    "inner join t_subject t4 on t4.id = t3.subject\n" +
                    "inner join t_area t5 on t1.areacode = t5.areacode \n");
            if(areaCode!=null && !"86".equals(areaCode)){
                stringBuilder.append(" and t5.areacode like concat('" + areaCode + "','%') \n");
                stringBuilder.append(" and t1.areacode like concat('" + areaCode + "','%') \n");
            }
            if(subject!=null){
                stringBuilder.append(" and t4.id = "+subject+" \n");
            }

            stringBuilder.append(" ) g \n  order by g.createtime desc;");
            return  stringBuilder.toString();
        }

        /* 获取美丽乡村列表 */
        public String getCountryListSQL(Map map) {
            StringBuilder stringBuilder=new StringBuilder();
            String areaCode=(String)map.get("areaCode");
            String tcode=(String)map.get("tcode");
            stringBuilder.append("select t1.id, t1.nickname,t1.pic titlepic,readtimes count_Num, t1.url,t2.mobile phone,t2.longitude lon,\n" +
                    "t2.latitude lat,t3.tcode code, t1.readtimes,t1.praisetimes,t1.ncommenttimes from t_countryside t1\n" +
                    "inner join t_user t2 on  t1.userid = t2.id\n" +
                    "inner join t_country_type t3 on t1.id = t3.cid\n");
            if(areaCode!=null && !"86".equals(areaCode)){
                stringBuilder.append(" and t2.areacode like concat('" + areaCode + "','%') ");
            }
            if(tcode!=null){
                stringBuilder.append(" and t3.tcode = "+tcode+" ");
            }
            stringBuilder.append(" order by readtimes desc;");
            return  stringBuilder.toString();
        }

        /* 获取美丽乡村类型列表 */
        public String getCountryTypeListSQL(Map map) {
            StringBuilder stringBuilder=new StringBuilder();
            String areaCode=(String)map.get("areaCode");
            stringBuilder.append("select t4.code,t4.name from t_countryside t1\n" +
                    "inner join t_user t2 on  t1.userid = t2.id \n" +
                    "inner join t_country_type t3 on t1.id = t3.cid\n" +
                    "inner join t_countryside_type t4 on t3.tcode = t4.code  ");
            if(areaCode!=null && !"86".equals(areaCode)){
                stringBuilder.append(" and t1.areacode like concat('" + areaCode + "','%') ");
            }

            stringBuilder.append("  group by t4.code;");
            return  stringBuilder.toString();
        }

        /* 更新好产品阅读、点赞、分享次数 */
        public String  updateGoodsReadSql(String gtype, String gid, String genre)
        {
            StringBuffer sql=new StringBuffer();
            sql.append("update ");
            if(gtype =="1")
            {
                sql.append(" cagmpisards.t_traceability p set ");
            }
            else
            {
                sql.append(" cagmpisards.t_goodproduct p set ");
            }

            if("1".equals(genre)){
                sql.append("readtimes =  if(readtimes is null,1,readtimes + 1) ");
            } else if ("2".equals(genre)){
                sql.append("praisetimes = if(praisetimes is null,1, praisetimes + 1) ");
            }else if ("3".equals(genre)){
                sql.append("ncommenttimes = if(ncommenttimes is null,1,ncommenttimes + 1) ");
            } else
            {
                sql.append("readtimes =  readtimes ");
            }
            sql.append("where id = '"+ gid +"' ;");

            return sql.toString();
        }

        /* 更新美丽乡村品阅读、点赞、分享次数 */
        public String  updateCountryReadSql(String cid,String ctype) {
            StringBuffer sql=new StringBuffer();
            sql.append("update cagmpisards.t_countryside c set ");
            if("1".equals(ctype)){
                sql.append("readtimes =  if(readtimes is null,1,readtimes + 1) ");
            } else if ("2".equals(ctype)){
                sql.append("praisetimes = if(praisetimes is null,1, praisetimes + 1) ");
            }else if ("3".equals(ctype)){
                sql.append("ncommenttimes = if(ncommenttimes is null,1,ncommenttimes + 1) ");
            } else
            {
                sql.append("readtimes =  readtimes ");
            }
            sql.append("where id = '"+ cid +"' ;");

            return sql.toString();
        }

        /* 公众服务产品(阅读、点赞、分享)数统计（省） */
        public String  getPublicProductCountSQL(String areaCode,String Ctype, Integer limit) {
            StringBuffer sql=new StringBuffer();
            sql.append("select product_name,product_read_count,product_praise_times,product_ncomment_times,product_mainpic  from\n" +
                    "(select p.product_name,p.product_read_count,p.product_praise_times,p.product_ncomment_times, " +
                    "concat('http://www.cqpisa.cn/cq',product_mainpic) product_mainpic from cagmpisards.t_area a\n" +
                    "inner join cqpamiss.t_b_productgroup g on find_in_set(a.areacode,g.fit_areaid)\n" +
                    "inner join cqpamiss.t_b_pms_product p on g.id = p.product_group\n" +
                    "where p.product_state = 6 and\n" +
                    "case \n" +
                    "when length('"+areaCode+"') = 2 then  a.areacode like concat('"+areaCode+"','%')\n" +
                    "when length('"+areaCode+"') = 4 then  a.areacode like concat('"+areaCode+"','%') or a.areacode = left(concat('"+areaCode+"','%'),2)\n" +
                    "when length('"+areaCode+"') = 6 then  a.areacode like concat('"+areaCode+"','%') or a.areacode = left(concat('"+areaCode+"','%'),2) or a.areacode = left(concat('"+areaCode+"','%'),4)\n" +
                    "end \n" +
                    "union \n" +
                    "select p.product_name,p.product_read_count,p.product_praise_times,p.product_ncomment_times," +
                    "'http://www.cqpisa.cn/cq/images/product_logo.jpg' as product_mainpic  from cagmpisards.t_area a\n" +
                    "inner join china_pamiss.t_b_productgroup g on find_in_set(a.areacode,g.fit_areaid)\n" +
                    "inner join china_pamiss.t_b_pms_product p on g.id = p.product_group\n" +
                    "where p.product_state = 6 and\n" +
                    "case \n" +
                    "when length('"+areaCode+"') = 2 then  a.areacode like concat('"+areaCode+"','%')\n" +
                    "when length('"+areaCode+"') = 4 then  a.areacode like concat('"+areaCode+"','%') or a.areacode = left(concat('"+areaCode+"','%'),2)\n" +
                    "when length('"+areaCode+"') = 6 then  a.areacode like concat('"+areaCode+"','%') or a.areacode = left(concat('"+areaCode+"','%'),2) or a.areacode = left(concat('"+areaCode+"','%'),4)\n" +
                    "end) v\n");
            if("1".equals(Ctype))
            {
                sql.append(" order by product_read_count desc\n" );
            } else if("2".equals(Ctype))
            {
                sql.append(" order by product_praise_times desc\n" );
            } else if("3".equals(Ctype))
            {
                sql.append(" order by product_ncomment_times desc\n" );
            }else
            {
                sql.append(" order by product_read_count desc\n" );
            }
            if(limit != null)
            {
                sql.append(" limit " + limit +" ;" );
            }
            return sql.toString();
        }

        /* 公众服务产品(阅读、点赞、分享)数统计（全国） */
        public String  getPublicProductCount86SQL(String Ctype, Integer limit) {
            StringBuffer sql=new StringBuffer();
            sql.append("select product_name,product_read_count,product_praise_times,product_ncomment_times,product_mainpic  from\n" +
                    "(select p.product_name,p.product_read_count,p.product_praise_times,p.product_ncomment_times," +
                    "concat('http://www.cqpisa.cn/cq',product_mainpic) product_mainpic from cqpamiss.t_b_productgroup g \n" +
                    "inner join cqpamiss.t_b_pms_product p on g.id = p.product_group\n" +
                    "where p.product_state = 6  \n" +
                    "union \n" +
                    "select  p.product_name,p.product_read_count,p.product_praise_times,p.product_ncomment_times," +
                    " 'http://www.cqpisa.cn/cq/images/product_logo.jpg' as product_mainpic  from china_pamiss.t_b_productgroup g \n" +
                    "inner join china_pamiss.t_b_pms_product p on g.id = p.product_group\n" +
                    "where p.product_state = 6) v\n");
            if("1".equals(Ctype))
            {
                sql.append(" order by product_read_count desc\n" );
            } else if("2".equals(Ctype))
            {
                sql.append(" order by product_praise_times desc\n" );
            } else if("3".equals(Ctype))
            {
                sql.append(" order by product_ncomment_times desc\n" );
            }else
            {
                sql.append(" order by product_read_count desc\n" );
            }
            if(limit != null)
            {
                sql.append(" limit " + limit +" ;" );
            }
            return sql.toString();
        }

        /* 好产品访问量、点赞数、分享数统计 */
        public String  getGoodsReadCountSQL(String areaCode,String gtype,Integer limit) {
            StringBuffer sql=new StringBuffer();
            sql.append("select name,url,readtimes,praisetimes,ncommenttimes,areacode from\n" +
                    "(select t1.name,t1.url,t1.readtimes,t1.praisetimes,t1.ncommenttimes,t2.code areacode from t_traceability t1\n" +
                    "inner join t_farmland t2 on t1.farm = t2.id\n" +
                    "union all \n" +
                    "select name,img url,readtimes,praisetimes,ncommenttimes,areacode from t_goodproduct) t \n where ");
            if("86".equals(areaCode))
            {
                sql.append(" 1=1 ");
            }
            else if(areaCode.length()==2)
            {
                sql.append(" areaCode like '"+ areaCode+"%'");
            }
            else if(areaCode.length()==4)
            {
                sql.append("(areaCode = left('"+areaCode+"',2)" + " or areaCode like '"+ areaCode+"%')");
            }
            else if(areaCode.length()==6)
            {
                sql.append("(areaCode = left('"+areaCode+"',2)" +" or areaCode = left('"+areaCode+"',4)" +  " or areaCode like '"+ areaCode+"%')");
            }

            if("1".equals(gtype))
            {
                sql.append(" order by readtimes desc\n" );
            } else if("2".equals(gtype))
            {
                sql.append(" order by praisetimes desc\n" );
            } else if("3".equals(gtype))
            {
                sql.append(" order by ncommenttimes desc\n" );
            }else
            {
                sql.append(" order by readtimes desc\n" );
            }
            if(limit != null)
            {
                sql.append(" limit " + limit +" ;" );
            }
            return sql.toString();
        }

        /* 美丽乡村访问量、点赞数、分享数统计 */
        public String  getCountryReadCountSQL(String areaCode,String ctype,Integer limit) {
            StringBuffer sql=new StringBuffer();
            sql.append("select t1.pic,t1.nickname,t1.readtimes,t1.praisetimes,t1.ncommenttimes,t3.name from t_countryside t1\n" +
                        "inner join t_country_type t2 on t1.id = t2.cid\n" +
                    "inner join t_countryside_type t3 on t2.tcode = t3.code \n where ");
            if("86".equals(areaCode))
            {
                sql.append(" 1=1 ");
            }
            else if(areaCode.length()==2)
            {
                sql.append(" areaCode like '"+ areaCode+"%'");
            }
            else if(areaCode.length()==4)
            {
                sql.append("(areaCode = left('"+areaCode+"',2)" + " or areaCode like '"+ areaCode+"%')");
            }
            else if(areaCode.length()==6)
            {
                sql.append("(areaCode = left('"+areaCode+"',2)" +" or areaCode = left('"+areaCode+"',4)" +  " or areaCode like '"+ areaCode+"%')");
            }

            if("1".equals(ctype))
            {
                sql.append(" order by readtimes desc\n" );
            } else if("2".equals(ctype))
            {
                sql.append(" order by praisetimes desc\n" );
            } else if("3".equals(ctype))
            {
                sql.append(" order by ncommenttimes desc\n" );
            }else
            {
                sql.append(" order by readtimes desc\n" );
            }
            if(limit != null)
            {
                sql.append(" limit " + limit +" ;" );
            }
            return sql.toString();
        }
    }
}


